package com.hongdee.crm.tdc.mapper;

import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * @author zhangkai
 * @desc 一句话描述
 **/
public class CallRecordSQL {

    /***
     * 查询回访记录SQL
     * @return
     */
    public String callRecordSQL(Map params) {

        String scoreSQL = new SQL() {{

            SELECT("  t1.order_num, t1.company_id, t1.narie_code, SUM(t1.score) AS score ");
            FROM(" t_market_dc_question_naire_order_info t1 ");
            WHERE(" t1.label_type = '问题项' ");
            WHERE(" t1.company_id = #{companyId}");
            /*** 工单号****/
            if (!StringUtils.isEmpty(params.get("orderNum"))) {
                WHERE(" t1.order_num like '%" + params.get("orderNum").toString() + "%' ");
            }

            GROUP_BY(" t1.company_id, t1.order_num, t1.narie_code ");

        }}.toString();

        return new SQL() {{
            SELECT(" u.u_name AS sa, tr.vin, tr.plateNum, c.owner_name, c.owner_tel");
            SELECT(" temp.order_num, tr.deal_date, temp.narie_code, temp.score");
            SELECT(" w.degree, w.type, w.content ,MAX(tr.task_count) AS callCount");
            FROM("(" + scoreSQL + ") temp ");
            LEFT_OUTER_JOIN(" t_crm_task_real tr ON temp.order_num = tr.order_num AND temp.company_id = tr.company_id  ");
            LEFT_OUTER_JOIN(" t_crm_car c ON tr.company_id = c.company_id AND tr.vin = c.vin ");
            LEFT_OUTER_JOIN(" t_bc_user u ON c.sa_id = u.id  ");
            LEFT_OUTER_JOIN(" t_market_whine w ON temp.company_id = w.company_id AND temp.order_num = w.order_num  AND tr.vin = w.vin AND w.degree IN ('抱怨', '建议') ");

            WHERE(" temp.company_id = #{companyId} ");
            /**
             * VIN
             * */
            if (!StringUtils.isEmpty(params.get("vin"))) {
                WHERE(" tr.vin like '%" + params.get("vin").toString() + "%' ");
            }

            /**
             * 车牌号
             * */
            if (!StringUtils.isEmpty(params.get("plateNum"))) {
                WHERE(" tr.plateNum like '%" + params.get("plateNum").toString() + "%' ");
            }

            /**
             * 车牌号
             * */
            if (!StringUtils.isEmpty(params.get("SA"))) {
                WHERE(" c.sa_id = #{SA} ");
            }


            GROUP_BY(" u.u_name, tr.vin, tr.plateNum, c.owner_name, c.owner_tel, temp.order_num, tr.deal_date, temp.narie_code, temp.score, w.degree, w.type, w.content ");


        }}.toString();


    }

}
